-- 明细导出
with site_modify as ( -- 修改线路段目的站点为发件下一站,并按单号粒度去重
    select
              shipment.shipment_no
            , shipment.shipment_name
            , shipment.billcode
            , shipment.start_site_code
            , shipment.start_site_name
            , shipment.start_site_sort_num
            , nvl(shipment.load_next_site_code,shipment.send_next_site_code) as end_site_code
            , nvl(shipment.load_next_site_name,shipment.send_next_site_name) as end_site_name
            , max(shipment.shipno_planned_departure_time)    as shipno_planned_departure_time
    from jms_dwd.dwd_wide_tms_road_waybill_dt shipment
    where   dt >= date_sub('{{ execution_date | cst_ds }}', 2) 
            and shipment.dt <= '{{ execution_date | cst_ds }}'
            and billcode is not null             -- 单号为null去除
            and substr(billcode, 1, 2) = 'JT'    -- 只要JT单号
            and shipment_state <> 5              -- 剔除作废任务号
            and is_trunk_branch = 1              -- 干线
            and nvl(shipment.load_next_site_code,shipment.send_next_site_code) is not null  -- 只取装车发件 
    group by 
            shipment.shipment_no
            , shipment.shipment_name
            , shipment.billcode
            , shipment.start_site_code
            , shipment.start_site_name
            , shipment.start_site_sort_num
            , nvl(shipment.load_next_site_code,shipment.send_next_site_code)
            , nvl(shipment.load_next_site_name,shipment.send_next_site_name)
), 
linepart_dim as ( -- 笛卡尔积获取线路段组合的累计里程以及一些时间字段
    select
        a.shipment_no              as shipment_no
        ,a.network_code            as start_site_code
        ,a.network_name            as start_site_name
        ,a.sort_num                as start_site_sort_num
        ,a.planned_departure_time  as linepart_planned_departure_time
        ,a.actual_departure_time   as linepart_actual_departure_time 
        ,b.network_code            as end_site_code
        ,b.network_name            as end_site_name
        ,b.sort_num                as end_site_sort_num
        ,b.planned_arrival_time    as linepart_planned_arrival_time  
        ,b.actual_arrival_time     as linepart_actual_arrival_time 
        -- ,b.acc_mileage - nvl(a.acc_mileage, 0)  as linepart_mileage -- 线路段里程(带累计)
        ,case when b.sort_num - a.sort_num = 1 then nvl(b.mileage,0) 
              else 0  
        end    as linepart_mileage
    from (
        select
            shipment_no --任务号
            ,sort_num
            ,network_code --经停网点编码
            ,network_name --经停网点名称
            ,mileage
            ,planned_departure_time
            ,actual_departure_time 
            ,planned_arrival_time
            ,actual_arrival_time 
        from jms_dwd.dwd_tmsnew_shipment_stop_union_base_dt  --任务单经停点
        where dt between date_sub('{{ execution_date | cst_ds }}',10) and '{{ execution_date | cst_ds }}'
        and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}',10) and '{{ execution_date | cst_ds }}'
        and table_type = 1
    ) a
    left join (
            select
                shipment_no --任务号
                ,sort_num
                ,network_code --经停网点编码
                ,network_name --经停网点名称
                ,mileage
                ,planned_departure_time
                ,actual_departure_time 
                ,planned_arrival_time
                ,actual_arrival_time 
            from jms_dwd.dwd_tmsnew_shipment_stop_union_base_dt  --任务单经停点
            where dt between date_sub('{{ execution_date | cst_ds }}',10) and '{{ execution_date | cst_ds }}'
            and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}',10) and '{{ execution_date | cst_ds }}'
            and table_type = 1
    ) b on a.shipment_no = b.shipment_no
       and a.sort_num < b.sort_num
     where b.network_code is not null 
)
-- 合并装车，中心发件，网点发件数据
,tab_loading_send as (
    select 
        shipment_no
        ,waybill_no
        ,scan_site_code
        ,scan_site
        ,next_station_code
        ,next_station
        ,scan_type
        ,scan_user_code
        ,scan_user
        ,scan_time
    from(
        select
            shipment_no
            ,waybill_no
            ,scan_site_code
            ,scan_site
            ,next_station_code
            ,next_station
            ,scan_type
            ,scan_user_code
            ,scan_user
            ,scan_time
            ,row_number() over(partition by shipment_no,waybill_no,scan_site_code order by scan_time desc) as row_id
        from (
        select
            shipment_no,waybill_no,package_code, scan_type,scan_site,scan_site_code
            ,next_station, next_station_code,scan_time
            ,scan_user_code,scan_user
            ,row_number() over(partition by shipment_no,waybill_no,scan_site_code order by scan_time desc) as row_id
            from jms_dwd.dwd_tab_barscan_loading_base_dt
        where dt >= date_add('{{ execution_date | cst_ds }}',-7)
        and shipment_no is not null and scan_site_code is not null --and waybill_no like 'JT%'
        union all
        -- 中心发件表
        select
            transfer_code as shipment_no,waybill_no,package_code, scan_type,scan_site,scan_site_code
            ,next_station, next_station_code,scan_time
            ,scan_user_code,scan_user
            ,row_number() over(partition by transfer_code,waybill_no,scan_site_code order by scan_time desc) as row_id
        from jms_dwd.dwd_tab_barscan_centersend_base_dt
        where dt >= date_add('{{ execution_date | cst_ds }}',-7)
        and transfer_code is not null and scan_site_code is not null-- and waybill_no like 'JT%'
        union all
        -- 网点发件表
        select
            shipment_no,waybill_no,package_code, scan_type,scan_site,scan_site_code
            ,next_station, next_station_code,scan_time
            ,scan_user_code,scan_user
            ,row_number() over(partition by shipment_no,waybill_no,scan_site_code order by scan_time desc) as row_id
        from jms_dwd.dwd_tab_barscan_sitesend_base_dt
        where dt >= date_add('{{ execution_date | cst_ds }}',-7)
        and shipment_no is not null and scan_site_code is not null-- and waybill_no like 'JT%'
        ) union_tmp
        where row_id = 1
    ) a 
    where 
        row_id = 1
)
insert overwrite table jms_dm.dm_tms_shipment_error_scan_detail_dt partition(dt)
select 
    a.shipment_no
    ,a.billcode           as waybill_no
    ,to_date(a.shipno_planned_departure_time) as shipno_planned_departure_date
    ,c.scan_site_code
    ,c.scan_site          as scan_site_name
    ,c.next_station_code  as next_site_code
    ,c.next_station       as next_site_name
    ,c.scan_type
    ,c.scan_user_code
    ,c.scan_user          as scan_user_name
    ,c.scan_time
    ,to_date(a.shipno_planned_departure_time)
from 
    site_modify a
    left join linepart_dim b
         on a.shipment_no     = b.shipment_no
        and a.start_site_code = b.start_site_code
        and a.end_site_code   = b.end_site_code
    inner join tab_loading_send c
         on a.shipment_no     = c.shipment_no
        and a.start_site_code = c.scan_site_code
        and a.billcode        = c.waybill_no
where 
    b.shipment_no is null 
distribute by 1
;

